package id.net.iconpln.bb.server.dao;

import id.net.iconpln.bb.server.util.CommonModule;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import oracle.jdbc.OracleTypes;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class PembayaranDao {
	@Autowired	private SessionFactory sessionFactory;
	
	
	public List<Map> getlistpembayaran(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        String sql = null;
        CallableStatement call;
        try {
           
        	System.out.println("kode :" + kode.toString() + " id_pltu : " + map.get("id_pltu").toString());
        	
            sql = "{call BBTRXPEMBAYARAN.SETID_PLTU(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("id_pltu").toString());
            call.execute();

        	
          	sql = "{? = call BBTRXPEMBAYARAN.GETLISTPEMBAYARAN(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setString(2, kode);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            lMap = CommonModule.convertResultsetToListLower(rset);
            
//            map.clear();
//            map.put("message", call.getString(1)); 
//            System.out.println("return: " + map.get("message").toString());
            
        }catch (Exception e) {
			e.printStackTrace();
		}
        
        return lMap;
	}

	
	public List<Map> insertpembayaran(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        try{
        	
        		
                
                if (map.get("crud").toString().equals("CREATE")){
                	
                	String sql = null;
                    CallableStatement call = null;
                	
                	sql = "{call BBTRXPEMBAYARAN.SETBA_SERAHTERIMA(?)}";
                    call = con.prepareCall(sql);
                    call.setString(1, map.get("bast").toString());
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETSERTIFIKAT_KUALITAS_BB(?)}";
                    call = con.prepareCall(sql);
                    call.setString(1, map.get("kualitasbb").toString());
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETSERTIFIKAT_KUANTITAS_BB(?)}";
                    call = con.prepareCall(sql);
                    call.setString(1, map.get("kuantitasbb").toString());
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETBA_KETERLAMBATAN(?)}";
                    call = con.prepareCall(sql);
                    call.setString(1, map.get("bak").toString());
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETCOPY_PJBB(?)}";
                    call = con.prepareCall(sql);
                    call.setString(1, map.get("pjbb").toString());
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETCOPY_PKP(?)}";
                    call = con.prepareCall(sql);
                    call.setString(1, map.get("pkp").toString());
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETCOPY_PEMBAYARAN_ROYALTI(?)}";
                    call = con.prepareCall(sql);
                    call.setString(1, map.get("bprt").toString());
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETPERPAJAKAN(?)}";
                    call = con.prepareCall(sql);
                    call.setString(1, map.get("pajak").toString());
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETNPWP(?)}";
                    call = con.prepareCall(sql);
                    call.setString(1, map.get("npwp").toString());
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETKWITANSI(?)}";
                    call = con.prepareCall(sql);
                    call.setString(1, map.get("kwitansi").toString());
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETASLI_FAKTUR_PAJAK(?)}";
                    call = con.prepareCall(sql);
                    call.setString(1, map.get("afp").toString());
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETRINCIAN_PERHITUNGAN_HARGA(?)}";
                    call = con.prepareCall(sql);
                    call.setString(1, map.get("rph").toString());
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETID_PENGIRIMAN(?)}";
                    call = con.prepareCall(sql);
                    call.setBigDecimal(1,  new BigDecimal(map.get("id_pjbb").toString()));
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETID_PJBB(?)}";
                    call = con.prepareCall(sql);
                    call.setBigDecimal(1,  new BigDecimal(map.get("id_pjbb").toString()));
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETID_PEMASOK(?)}";
                    call = con.prepareCall(sql);
                    call.setBigDecimal(1,  new BigDecimal(map.get("id_pjbb").toString()));
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETID_PLTU(?)}";
                    call = con.prepareCall(sql);
                    call.setBigDecimal(1,  new BigDecimal(map.get("id_pltu").toString()));
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETTANGGAL(?)}";
                    call = con.prepareCall(sql);
                    DateFormat df = new SimpleDateFormat("dd/mm/yyyy");
                    java.util.Date tgl = df.parse(map.get("tgl").toString());
                    call.setDate(1, new java.sql.Date(tgl.getTime()));
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETLAMPIRAN(?)}";
                    call = con.prepareCall(sql);
                    call.setString(1, "");
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETTOTAL_INVOICE(?)}";
                    call = con.prepareCall(sql);
                    call.setBigDecimal(1,  new BigDecimal(map.get("total").toString()));
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETTANGGAL_BAYAR(?)}";
                    call = con.prepareCall(sql);
                    DateFormat df1 = new SimpleDateFormat("dd/mm/yyyy");
                    java.util.Date tgl1 = df.parse(map.get("tgl").toString());
                    call.setDate(1, new java.sql.Date(tgl1.getTime()));
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETCREATEBY(?)}";
                    call = con.prepareCall(sql);
                    call.setString(1, map.get("createby").toString());
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETAPP_ACCESS(?)}";
                    call = con.prepareCall(sql);
                    call.setString(1, map.get("app_access").toString());
                    call.execute();
                    
                    sql = "{? = call BBTRXPEMBAYARAN.CRUDPEMBAYARAN(?)}";
                    call = con.prepareCall(sql);
                    call.registerOutParameter(1, OracleTypes.VARCHAR);
                    call.setString(2, map.get("crud").toString());
                    call.execute();
                    
                    map.clear();
                    map.put("message", call.getString(1)); 
                    System.out.println("return: " + map.get("message").toString());
                	
                }else if (map.get("crud").toString().equals("UPDATE")){
                	
                	String sql = null;
                    CallableStatement call = null;
                	
                	sql = "{call BBTRXPEMBAYARAN.SETNO_INVOICE(?)}";
                    call = con.prepareCall(sql);
                    call.setString(1, map.get("no_invoice").toString());
                    call.execute();
                	
                	sql = "{call BBTRXPEMBAYARAN.SETUPDATEBY(?)}";
                    call = con.prepareCall(sql);
                    call.setString(1, map.get("updateby").toString());
                    call.execute();
                    
                    sql = "{call BBTRXPEMBAYARAN.SETAPP_ACCESS(?)}";
                    call = con.prepareCall(sql);
                    call.setString(1, map.get("app_access").toString());
                    call.execute();
                    
                    sql = "{? = call BBTRXPEMBAYARAN.CRUDPEMBAYARAN(?)}";
                    call = con.prepareCall(sql);
                    call.registerOutParameter(1, OracleTypes.VARCHAR);
                    call.setString(2, map.get("crud").toString());
                    call.execute();
                    
                    map.clear();
                    map.put("message", call.getString(1)); 
                    System.out.println("return: " + map.get("message").toString());
                }
            
        }catch (Exception e) {
			e.printStackTrace();
			map.clear();
            map.put("message", e.getMessage());  
		}
        lMap.add(map);
        return lMap;
	}
	
	
}
